﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Management
{
    public partial class Information : Form
    {
        public Information()
        {
            InitializeComponent();
            ShowStudentInfo();

        }
        private string connectionString = "Data Source=D:\\Project\\VC\\Net\\Management\\STUManagement.db;Version=3;";


        // 显示学生信息
        private void ShowStudentInfo()
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT StudentID, StudentName, Age, ClassID, Sex FROM Student";
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, connection))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    dataGridView1.DataSource = dt;
                }
            }
        }

        // 查询学生信息
        private void buttonQuery_Click(object sender, EventArgs e)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT StudentID, StudentName, Age, ClassID, Sex FROM Student";
                if (!string.IsNullOrEmpty(textBox1.Text))
                {
                    query += $" WHERE StudentName = '{textBox1.Text}'";
                }
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, connection))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    dataGridView1.DataSource = dt;
                }
            }
        }

        // 排序学生信息
        private void buttonSort_Click(object sender, EventArgs e)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT StudentID, StudentName, Age, ClassID, Sex FROM Student";
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, connection))
                {
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    dt.DefaultView.Sort = "StudentName ASC"; // Sort by student name as an example
                    dataGridView1.DataSource = dt;
                }
            }
        }

        // 增加学生信息
        private void buttonAdd_Click(object sender, EventArgs e)
        {
            // 打开学生信息录入窗口
            new Input().Show();
        }

        // 删除学生信息
        private void buttonDelete_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count > 0)
            {
                int studentId = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells["StudentID"].Value);
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    string deleteQuery = $"DELETE FROM Student WHERE StudentID = {studentId}";
                    using (SQLiteCommand command = new SQLiteCommand(deleteQuery, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
                ShowStudentInfo();
            }
        }

        // 更新学生信息
        private void buttonUpdate_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedRows.Count > 0)
            {
                int studentId = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells["StudentID"].Value);
                string updateQuery = $"UPDATE Student SET StudentName = '{dataGridView1.SelectedRows[0].Cells["StudentName"].Value}', " +
                    $"Age = {dataGridView1.SelectedRows[0].Cells["Age"].Value}, " +
                    $"ClassID = {dataGridView1.SelectedRows[0].Cells["ClassID"].Value}, " +
                    $"Sex = '{dataGridView1.SelectedRows[0].Cells["Sex"].Value}' " +
                    $"WHERE StudentID = {studentId}";
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    using (SQLiteCommand command = new SQLiteCommand(updateQuery, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
                ShowStudentInfo();
            }
        }

        private void buttonLog_Click(object sender, EventArgs e)
        {
           
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    string logQuery = "SELECT  Action, Timestamp FROM Log";
                    using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(logQuery, connection))
                    {
                        DataTable dt = new DataTable();
                        adapter.Fill(dt);
                    dataGridView1.DataSource = dt;
                    }
                }
            

        }
    }
}
